package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.顾客记录汇总Model;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.List;

/**
 * Created by sxy on 2016/7/12.
 */
public interface 顾客记录汇总Repository extends JpaRepository<顾客记录汇总Model, Integer>, JpaSpecificationExecutor<顾客记录汇总Model> {
    @Query("select a from 顾客记录汇总Model a where a.电话号码=?1 and a.许可=?2")
    顾客记录汇总Model findOneCus(String phone, String permit);

    @Query("select a from 顾客记录汇总Model a where   a.顾客编号=?1 ")
    顾客记录汇总Model findOneCusAndpermitandusername(String uid);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.所属销售顾问=?2 where a.顾客编号=?1")
    int updateAssignById(int ids, String name);

    @Query("select a from  顾客记录汇总Model a where a.电话号码=?1 and a.许可=?2")
    顾客记录汇总Model findCusByPhoneAndPermit(String phone,String permit);

    /**
     * 获取顾客记录
     * @param customerId
     * @param account
     * @return
     */
    @Query("select a from  顾客记录汇总Model a where a.顾客编号=?1 and a.销售顾问=?2")
    顾客记录汇总Model findCusByCustomerAndAccount(Integer customerId,String account);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.裁决时间 = ?1 where a.顾客编号 = ?2 and a.许可 = ?3 and a.顾客姓名 = ?4 and a.电话号码 = ?5 and a.销售顾问 = ?6")
    int updateReArbitrateDetailDate(Timestamp aDate, Integer cusId, String permit, String cusName, String cusTel, String saleMan);

    @Modifying
    @Query("update 顾客记录汇总Model a  set    a.销售顾问 = ?1 ,  a.所属销售顾问=?2 where a.顾客编号 = ?3 and a.许可 = ?4 and a.顾客姓名 = ?5 and a.电话号码 = ?6  ")
    int updateReArbitrateDetailInfo(String saleMan, String saleMans, Integer cusId, String permit, String cusName, String cusTel);


    @Modifying
    @Query("update 顾客记录汇总Model set 裁决时间 = ?1, 所属销售顾问 = ?2 where 许可 = ?3 and 电话号码 = ?4")
    int lastUpdateReArbitrateDetailDate(Timestamp a, String newSales, String permit, String cusTel);

    @Query("select a from 顾客记录汇总Model a where a.电话号码=?1 and a.许可=?2 and a.更新时间 >= ?3")
    顾客记录汇总Model findCusByPhoneAndPermitAndDate(String phone, String permit, Timestamp date);

    @Query("select a from 顾客记录汇总Model a where a.顾客编号=?1")
    顾客记录汇总Model findCusByCustomerID(int customerId);

    @Query("select a from 顾客记录汇总Model a where a.所属销售顾问=?1")
    List<顾客记录汇总Model> findCustomersByAccount(String account);

    @Query("select a.顾客性别 from 顾客记录汇总Model a where a.所属销售顾问=?1 and a.电话号码=?2")
    String findCusByCustomerIDAndPhone(String saleMans,String phone);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.新车销售状态=?2 where a.顾客编号=?1")
    int updteNewCarStatusById(int id,int status);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.所属销售顾问=?2,a.crm战败确认=2,a.更新时间 = current_time where a.顾客编号=?1")
    int updateCusAboutSC(Integer custid,String sccode);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.crm战败确认=0,a.更新时间 = current_time where a.顾客编号=?1")
    int updateCRMConfirmFail(Integer custid);

    @Modifying
    @Query("update 顾客记录汇总Model a set a.crm战败确认=1,a.顾客状态='本月',a.更新时间 = current_time where a.顾客编号=?1")
    int updateCRMActivation(Integer custid);

    @Query("select a from 顾客记录汇总Model a where a.许可 = ?1")
    List<顾客记录汇总Model> getCusMoListByPermit(String permit);
}
